﻿/*
Project:       
File:          RegisterDAL.cs
Description:   
*               
*               
*               
Author:        Lanny
Date:          July,2014
version:       2.0
Modify:
 
                 
Comments:
* 1. 2014.07.20.  增加了一种搜索模式~ 提炼了一些code。

 
*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using SQLServerDAL;
using System.Data.SqlClient;
using System.Collections;
using System.Xml;
using System.Diagnostics;
using DataAnalyseTool.IDAL;
using DataAnalyseTool.Model;

namespace DataAnalyseTool.Repository
{
  public class RegisterDAL : IRegisterDAL
  {
    private string userName = "选择用户";
    private string shopID = "100000";

    private List<string> existQQList = new List<string>();

    public RegisterDAL()
    {

    }

    public string GetQQByBankName(string bankname)
    {
      string strSql = "select QQ from ProjectFileds Where BankName = N'" + bankname + "'";
      DataTable dt = SQLHelper.ExecuteDt(strSql);
      string temp = null;
      if (dt.Rows.Count > 0)
      {
        for (int i = 0; i < dt.Rows.Count; i++)
        {
          temp += dt.Rows[i]["QQ"] + ",";
        }
        return temp.Contains(',') ? temp.Remove(temp.Length - 1) : temp;
        // return dt.Rows[0]["QQ"].ToString();
      }
      return "";
    }
    public DataTable GetAllRegister()
    {
      string strSql = "select * from ProjectFileds,Shops where ProjectFileds.ShopID = Shops.ShopID order by ProID desc";
      return SQLHelper.ExecuteDt(strSql);
    }

    public DataSet GetAllRegisterByPagination(string tablename1, string tablename2, string selectfield, string sqlWhere, string orderbyfield, int pagesiz, int page, out int Count)
    {
      DataSet ds = new DataSet();
      SqlParameter[] mypara = { 
                                    new SqlParameter("@tblName",SqlDbType.Char,20),
                                    new SqlParameter("@tblName1",SqlDbType.Char,200),
                                    new SqlParameter("@SelectFieldName",SqlDbType.Char,200),  
                                    new SqlParameter("@strWhere",SqlDbType.Char,500),
                                    new SqlParameter("@OrderFieldName",SqlDbType.Char,200),
                                    new SqlParameter("@PageSize",SqlDbType.Int,100),
                                    new SqlParameter("@PageIndex",SqlDbType.Int,100),
                                    new SqlParameter("@iRowCount",SqlDbType.Int,100),
                                    };
      mypara[0].Value = tablename1;
      mypara[1].Value = tablename2;
      mypara[2].Value = selectfield;
      mypara[3].Value = sqlWhere;
      mypara[4].Value = orderbyfield;
      mypara[5].Value = pagesiz;
      mypara[6].Value = page;
      mypara[7].Direction = ParameterDirection.Output;
      SQLHelper sqlHelper = new SQLHelper();
      ds = sqlHelper.RunProc("GetAllRegisterPro", mypara, "ProjectFileds");
      Count = Convert.ToInt32(mypara[7].Value.ToString());
      return ds;

    }

    public DataSet GetAllRegisterByPagination(string strsql, string selectfield, string sqlWhere, string orderbyfield, int pagesiz, int page, out int Count)
    {
      DataSet ds = new DataSet();
      SqlParameter[] mypara = { 
                                    new SqlParameter("@customerSql",SqlDbType.NVarChar,2000),
                                    new SqlParameter("@SelectFieldName",SqlDbType.Char,200),  
                                    new SqlParameter("@strWhere",SqlDbType.Char,500),
                                    new SqlParameter("@OrderFieldName",SqlDbType.Char,200),
                                    new SqlParameter("@PageSize",SqlDbType.Int,100),
                                    new SqlParameter("@PageIndex",SqlDbType.Int,100),
                                    new SqlParameter("@iRowCount",SqlDbType.Int,100),
                                    };
      mypara[0].Value = strsql;
      mypara[1].Value = selectfield;
      mypara[2].Value = sqlWhere;
      mypara[3].Value = orderbyfield;
      mypara[4].Value = pagesiz;
      mypara[5].Value = page;
      mypara[6].Direction = ParameterDirection.Output;
      SQLHelper sqlHelper = new SQLHelper();
      ds = sqlHelper.RunProc("GetAllRegisterByParaPro", mypara, "ProjectFileds");
      Count = Convert.ToInt32(mypara[6].Value.ToString());
      return ds;

    }


    public DataTable GetAllRegisterBySearchPara(SearchPara sp)
    {
      string datefrom = sp.datefrom == "" ? "1900-01-01 00:00:00" : Convert.ToDateTime(sp.datefrom).ToShortDateString() + " 00:00:00";
      string dateto = sp.dateto == "" ? "9999-01-01 00:00:00" : Convert.ToDateTime(sp.dateto).ToShortDateString() + " 23:59:59";
      int pricefrom = sp.pricefrom == "" ? Int32.MinValue : Convert.ToInt32(sp.pricefrom);
      int priceto = sp.priceto == "" ? Int32.MaxValue : Convert.ToInt32(sp.priceto);

      string limitdate = DateTime.Now.AddDays(0 - sp.limitdays).ToShortDateString();

      string strSql1 = "select * from ProjectFileds inner join Shops on ProjectFileds.ShopID = Shops.ShopID where ProjectFileds.Date>@datefrom and ProjectFileds.Date<@dateto and ProjectFileds.Date<=@limitdate and ProjectFileds.Cash >= @pricefrom and ProjectFileds.Cash <= @priceto";



      string strSql2 = "select * from ProjectFileds inner join Shops on ProjectFileds.ShopID = Shops.ShopID";
      string str = "";
      if (sp.keywords != string.Empty)
      {
        if (sp.haschecked == "true")
        {
          if (sp.chkqq == "1")
          {


            str += " or ProjectFileds.QQ like @keyword";
          }
          if (sp.chbcaifutong == "1")
          {
            str += " or ProjectFileds.CaifutongAccount like @keyword";

          }
          if (sp.chbcaifutongname == "1")
          {
            str += " or ProjectFileds.CaifutongName like @keyword";
          }
          if (sp.chbzhifubaoname == "1")
          {
            str += " or ProjectFileds.ZhifubaoName like @keyword";
          }
          if (sp.chbzhifubaoaccount == "1")
          {
            str += " or ProjectFileds.ZhifubaoAccount like @keyword";
          }
          if (sp.chbfkzhifubao == "1")
          {
            str += " or ProjectFileds.RebateZhifubao like @keyword";
          }
          if (sp.chbfkzhifubaoname == "1")
          {
            str += " or ProjectFileds.RebateZhifubaoName like @keyword";
          }
        }
        else
        {
          str += " or ProjectFileds.QQ like @keyword or ProjectFileds.CaifutongAccount like @keyword or ProjectFileds.CaifutongName like @keyword or ProjectFileds.ZhifubaoName like @keyword or ProjectFileds.ZhifubaoAccount like @keyword or ProjectFileds.RebateZhifubao like @keyword or ProjectFileds.RebateZhifubaoName like @keyword or ProjectFileds.Wangwang like @keyword or ProjectFileds.Cellphone like @keyword or ProjectFileds.Fixphone like @keyword or ProjectFileds.ReceiveName like @keyword or ProjectFileds.ReceiveAddress like @keyword or ProjectFileds.ReceiveCellphone like @keyword or ProjectFileds.ReceiveFixphone like @keyword or ProjectFileds.Province like @keyword or ProjectFileds.City like @keyword or ProjectFileds.District like @keyword or ProjectFileds.Beizhu like @keyword or ProjectFileds.BankAccount like @keyword or  ProjectFileds.BankName like @keyword or Shops.ShopName like @keyword";
        }

        if (str != string.Empty)
        {
          str = str.Substring(str.IndexOf("or") + 2, str.Length - str.IndexOf("or") - 2);

          strSql2 = strSql2 + " where " + str;
        }
      }




      SqlParameter[] para;
      if (str != string.Empty)
      {
        para = new SqlParameter[]
             {
                new SqlParameter ("@datefrom",datefrom),
                new SqlParameter ("@dateto",dateto),      
                new SqlParameter ("@pricefrom",pricefrom),      
                new SqlParameter ("@priceto",priceto),
                new SqlParameter("@limitdate",limitdate),
                new SqlParameter("@keyword","%"+sp.keywords+"%")
             };
      }
      else
      {
        para = new SqlParameter[]{
                  new SqlParameter ("@datefrom",datefrom),
                  new SqlParameter ("@dateto",dateto),      
                  new SqlParameter ("@pricefrom",pricefrom),      
                  new SqlParameter ("@priceto",priceto),
                  new SqlParameter("@limitdate",limitdate)

	            };
      }

      string strSql = strSql1 + " intersect " + strSql2;

      return SQLHelper.ExecuteDt(strSql, para);
    }
    public string getStrSqlBuKeyword(string field, string value)
    {
      string strSql = "";
      if (field == "QQ")
      {
        strSql = "and ProjectFileds.QQ = N'" + value + "'";
      }
      if (field == "旺旺")
      {
        strSql = "and ProjectFileds.Wangwang = N'" + value + "'";
      }
      if (field == "绑定支付宝账号")
      {
        strSql = "and ProjectFileds.ZhifubaoAccount = N'" + value + "'";
      }
      if (field == "绑定支付宝账号姓名")
      {
        strSql = "and ProjectFileds.ZhifubaoName = N'" + value + "'";
      }
      if (field == "收款支付宝账号")
      {
        strSql = "and ProjectFileds.RebateZhifubao = N'" + value + "'";
      }
      if (field == "收款支付宝账号姓名")
      {
        strSql = "and ProjectFileds.RebateZhifubaoName = N'" + value + "'";
      }
      if (field == "电话")
      {
        strSql = "and ProjectFileds.ReceiveCellphone = N'" + value + "'";
      }
      if (field == "银行账号")
      {
        strSql = "and ProjectFileds.BankAccount = N'" + value + "'";
      }
      if (field == "银行户名")
      {
        strSql = "and ProjectFileds.BankName = N'" + value + "'";
      }
      if (field == "何种银行")
      {
        strSql = "and ProjectFileds.BankType = N'" + value + "'";
      }
      return strSql;
    }
    private bool CheckAllIsNumbers(string str)
    {
      bool isFlag = true;
      foreach (char item in str)
      {
        if (item == ',')
          continue;
        if (!(item >= 48 && item <= 58))
        {
          isFlag = false;
        }
      }
      return isFlag;
    }
    public DataTable GetAllMoney(SearchPara sp)
    {
      string strSql4 = null;
      string strSql5 = null;
      string datefrom = sp.datefrom == "" ? "1900-01-01 00:00:00" : Convert.ToDateTime(sp.datefrom).ToShortDateString() + " 00:00:00";
      string dateto = sp.dateto == "" ? "9999-01-01 00:00:00" : Convert.ToDateTime(sp.dateto).ToShortDateString() + " 23:59:59";
      strSql5 = " ProjectFileds.Date>='" + datefrom + "' and ProjectFileds.Date<='" + dateto + "'";


      if ((sp.userName != "" && sp.userName != "选择用户") || (sp.shopId != "" && sp.shopId != "100000"))
      {
        if (sp.userName != "" && sp.userName != "选择用户" && sp.shopId != "" && sp.shopId != "100000")
        {
          strSql4 = "select Cash from ProjectFileds inner join Shops on ProjectFileds.ShopID = Shops.ShopID where ProjectFileds.ShopID=" + sp.shopId + " and ProjectFileds.CreatedBy='" + sp.userName + "'";
        }
        if (sp.userName != "" && sp.userName != "选择用户" && sp.shopId == "100000")
        {
          strSql4 = "select Cash from  ProjectFileds inner join Shops on ProjectFileds.ShopID = Shops.ShopID where ProjectFileds.CreatedBy='" + sp.userName + "'";
        }
        if (sp.userName == "选择用户" && sp.shopId != "100000")
        {
          strSql4 = "select Cash from ProjectFileds inner join Shops on ProjectFileds.ShopID = Shops.ShopID where ProjectFileds.ShopID=" + sp.shopId + "";
        }
      }
      if (strSql5 != null)
        strSql4 += " and" + strSql5;
      return SQLHelper.ExecuteDt(strSql4);
    }
    private string GetBasicQueryStrWithCondition(SearchPara sp, ref string limit)
    {
      string basicStr, datefrom, dateto;
      int pricefrom, priceto;

      datefrom = sp.datefrom == "" ? "1900-01-01 00:00:00" : Convert.ToDateTime(sp.datefrom).ToShortDateString() + " 00:00:00";
      dateto = sp.dateto == "" ? "9999-01-01 00:00:00" : Convert.ToDateTime(sp.dateto).ToShortDateString() + " 23:59:59";
      pricefrom = sp.pricefrom == "" ? Int32.MinValue : Convert.ToInt32(sp.pricefrom);
      priceto = sp.priceto == "" ? Int32.MaxValue : Convert.ToInt32(sp.priceto);
      //limit = DateTime.Now.AddDays(0 - sp.limitdays + 1).ToShortDateString();

      //basicStr = @"select ProjectFileds.*,Shops.ShopName,Shops.ShopColor from ProjectFileds inner join Shops on ProjectFileds.ShopID = Shops.ShopID where ProjectFileds.Date>='" + datefrom + "' and ProjectFileds.Date<='" + dateto + "' and ProjectFileds.Date<='" + limit + "' and ProjectFileds.Cash >= " + pricefrom + " and ProjectFileds.Cash <= " + priceto + "";
      basicStr = @"select ProjectFileds.*,Shops.ShopName,Shops.ShopColor from ProjectFileds inner join Shops on ProjectFileds.ShopID = Shops.ShopID where ProjectFileds.Date>='" + datefrom + "' and ProjectFileds.Date<='" + dateto + "' and ProjectFileds.Cash >= " + pricefrom + " and ProjectFileds.Cash <= " + priceto + "";
      return basicStr;
    }
    //True  First mode; 
    //False Second mode.
    private bool checkFirstMode(string[] arr)
    {
      return arr[2].Contains("银行户名") && arr[3].Contains("银行账号") && arr[4].Contains("何种银行");
    }
    private bool checkSecondMode(string[] arr)
    {
      return arr[2].Contains("旺旺绑定支付宝") && arr[3].Contains("支付宝名字") && arr[4].Contains("返款财付通");
    }
    private bool checkThirdMode(string[] arr)
    {
      return arr[2].Contains("旺旺绑定支付宝") && arr[3].Contains("支付宝名字") && arr[4].Contains("返款银行卡及户名") && arr[5].Contains("何种银行");
    }
    private string getStrBySpecialMode(string[] arry, string searchWord)
    {
      string needSearchWord = "";
      string tempStr = "";
      for (int i = 0; i < arry.Length; i++)
      {
        string[] strRecord = arry[i].Split(':').Length == 1 ? arry[i].Split('：') : arry[i].Split(':');
        if (strRecord[0].Trim() == searchWord)
        {
          needSearchWord = strRecord[1];
          break;
        }
      }

      string allSeachersItems = getDefineSearchItems();
      string[] allItems = allSeachersItems.Split(';');
      foreach (string item in allItems)
        tempStr += @" or ProjectFileds." + item + " = N'" + needSearchWord + "'";
      return tempStr;
    }
    private string getStrByFirstMode(string[] arry)
    {
      string str = "";
      string tempQQ = "";
      List<string> allRelationQQs = null;
      for (int i = 0; i < arry.Length; i++)
      {
        string[] strRecord = arry[i].Split(':').Length == 1 ? arry[i].Split('：') : arry[i].Split(':');
        if (strRecord[0].Trim() == "QQ")
        {
          //flagFirstMode = true;
          str += getStrSqlBuKeyword(strRecord[0].Trim(), strRecord[1].Trim());
          tempQQ = strRecord[1];
          break;
        }
      }
      allRelationQQs = GetAllRelationQQs(tempQQ);
      if (allRelationQQs.Count == 0)
        str += @" or ProjectFileds.QQ = N'" + tempQQ + "'";
      else
      {
        foreach (string qq in allRelationQQs)
          str += @" or ProjectFileds.QQ = N'" + qq + "'";
      }

      return str;
    }
    private string removeAndOr(string[] allStrss, string strr)
    {
      if (allStrss[1] == "or")
        strr = strr.Substring(strr.IndexOf("or") + 2, strr.Length - strr.IndexOf("or") - 2);
      if (allStrss[0] == "and")
        strr = strr.Substring(strr.IndexOf("and") + 3, strr.Length - strr.IndexOf("and") - 3);
      return strr;
    }
    private string getDefineSearchItems()
    {
      XmlDocument xml = new XmlDocument();
      string configPath = AppDomain.CurrentDomain.BaseDirectory + "Config\\DefineSearchItems.xml";
      xml.Load(configPath);
      XmlNode root = xml.SelectSingleNode("Projects");
      XmlNodeList lists = root.ChildNodes;

      string sb = "";
      for (int i = 0; i < lists.Count; i++)
      {
        if (lists[i].Attributes["isSearch"].Value == "true")
          sb += lists[i].Name + ";";
      }
      if (sb != "")
        sb = sb.Substring(0, sb.Length - 1);
      return sb;

    }

    private string getStrWithouSearchItems(string keyword)
    {
      string str = "";

      str += @" or ProjectFileds.QQ = N'" + keyword + "' or ProjectFileds.CaifutongAccount = N'" + keyword + "' or ProjectFileds.CaifutongName = N'" + keyword + "' or ProjectFileds.ZhifubaoName = N'" + keyword + "' or ProjectFileds.ZhifubaoAccount = N'" + keyword + "' or ProjectFileds.RebateZhifubao = N'" + keyword + "' or ProjectFileds.RebateZhifubaoName = N'" + keyword + "' or ProjectFileds.Wangwang = N'" + keyword + "' or ProjectFileds.Cellphone = N'" + keyword + "' or ProjectFileds.Fixphone = N'" + keyword + "' or ProjectFileds.ReceiveName = N'" + keyword + "' or ProjectFileds.ReceiveAddress = N'" + keyword + "' or ProjectFileds.ReceiveCellphone = N'" + keyword + "' or ProjectFileds.ReceiveFixphone = N'" + keyword + "' or ProjectFileds.Province = N'" + keyword + "' or ProjectFileds.City = N'" + keyword + "' or ProjectFileds.District = N'" + keyword + "' or ProjectFileds.Beizhu = N'" + keyword + "' or ProjectFileds.BankAccount = N'" + keyword + "' or  ProjectFileds.BankName = N'" + keyword + "' or Shops.ShopName = N'" + keyword + "' or ProjectFileds.BankType = N'" + keyword + "' or ProjectFileds.Source = N'" + keyword + "'";

      return str;
    }

    //单击“新增登记”按钮。。判断QQ或者支付宝名字
    private string getStrDirectAddRegisterData(SearchPara sp)
    {
      string tempStr = "";

      if (sp.chkqq != "" && sp.chbfkzhifubaoname != "")
        tempStr = "ProjectFileds.QQ = N'" + sp.chkqq + "' or ProjectFileds.ZhifubaoName = N'" + sp.chbzhifubaoname + "'";
      else if (sp.chkqq != "")
        tempStr = "ProjectFileds.QQ = N'" + sp.chkqq + "'";
      else if (sp.chbzhifubaoname != "")
        tempStr = "ProjectFileds.ZhifubaoName = N'" + sp.chbzhifubaoname + "'";

      return tempStr;
    }
    public DataTable GetAllRegisterBySearchPara(SearchPara sp, int pagesize, int page, out int count)
    {
      string sbQuery, limitdate, basicQueryStr, basicQueryStrWithCondition, queryStrSelectUserAndShop, queryStrQQRelation, noSourceSqlStr, lastSqlStr, sourceStr;
      //StringBuilder sbQuery;
      QQRelationDal dal;
      List<string> allRelationQQs;
      limitdate = null;
      dal = new QQRelationDal();
      allRelationQQs = new List<string>();

      queryStrSelectUserAndShop = "";
      queryStrQQRelation = "";
      sbQuery = "";

      basicQueryStr = @"select ProjectFileds.*,Shops.ShopName,Shops.ShopColor from ProjectFileds inner join Shops on ProjectFileds.ShopID = Shops.ShopID";
      basicQueryStrWithCondition = GetBasicQueryStrWithCondition(sp, ref limitdate);
      if (sp.keywords != string.Empty)
      {
        #region 需要判断三种模式！
        // 需要判断两种匹配模式！~
        //第一个匹配模式，对一下数据进行搜索，那么只通过 【QQ】 进行查找。
        //QQ：280671234
        //旺旺：户外运动梁爽
        //银行户名：万杰
        //银行账号：6228480688031687876
        //何种银行：中国农业银行
        //现在要改成 

        //第二个匹配模式 对以下一组数据进行搜索，那么只搜索【支付宝】名字
        //QQ:
        //旺旺：
        //旺旺绑定支付宝：
        //支付宝名字：
        //返款财付通:

        //第三个匹配模式 对以下一组数据进行搜索，那么只搜索【支付宝】名字
        //QQ:
        //旺旺：
        //旺旺绑定支付宝：
        //支付宝名字：
        //返款银行卡及户名：
        //何种银行：

        #endregion
        if (sp.keywords.Split('\n').Length > 1)
        {
          string[] arry = sp.keywords.Split('\n');
          // string tempQQ = null;
          bool flagFirstMode = false; bool flagSecondMode = false; bool flagThridMode = false;
          if (checkFirstMode(arry))
          {
            flagFirstMode = true;
            sbQuery += getStrByFirstMode(arry);
          }

          if (checkSecondMode(arry))
          {
            flagSecondMode = true;
            sbQuery += getStrBySpecialMode(arry, "支付宝名字");
          }
          //2014.07.20 Modify --Begin.
          if (checkThirdMode(arry))
          {
            flagThridMode = true;
            sbQuery += getStrBySpecialMode(arry, "支付宝名字");
          }
          //2014.07.20 Modify --End.

          //我也不知道为什么 需要这样做。。但是这样确实提高了查询速度。。
          if (!flagFirstMode && !flagSecondMode && !flagThridMode)
            sbQuery += @" or ProjectFileds.ReceiveName" + " = N'" + "测试" + "'";
        }
        else
        {
          if (sp.haschecked == "true")
          {
            string[] scopeArry = sp.searchScope.Split(';');
            List<string> scopeList = scopeArry.ToList();
            if (scopeList.Count >= 1)
            {
              for (int i = 0; i < scopeList.Count; i++)
              {
                if (scopeList[i] == "QQ")
                {
                  if (CheckAllIsNumbers(sp.keywords))
                  {
                    allRelationQQs = GetAllRelationQQs(sp.keywords);
                    if (allRelationQQs.Count == 0)
                      sbQuery += @" or ProjectFileds.QQ = N'" + sp.keywords + "'";
                    else
                    {
                      foreach (string qq in allRelationQQs)
                        sbQuery += @" or ProjectFileds.QQ = N'" + qq + "'";
                    }
                  }
                }
                else if (scopeList[i] == "ShopID")
                  sbQuery += @" or Shops.ShopName = N'" + sp.keywords + "'";
                else
                  sbQuery += @" or ProjectFileds." + scopeList[i] + " = N'" + sp.keywords + "'";
              }
            }
          }
          else
          {
            //这是如果客户没有选择 任何必须搜索的项(DefineSearchItems.xml) 之后。就用这个。不过，这种情况很少出现。
            sbQuery += getStrWithouSearchItems(sp.keywords);
          }
        }
        if (sbQuery != string.Empty)
        {
          string[] allStrs = sbQuery.Split(' ');
          if (allStrs.Length > 0)
            sbQuery = removeAndOr(allStrs, sbQuery);
          // basicQueryStr = basicQueryStr + " where " + str;
        }
      }
      else
      {
        //这里 主要是为了通过“新增登记”添加 登记 的时候。。。
        if (sp.isDirectAddRegisterFlag != "")
          sbQuery += getStrDirectAddRegisterData(sp);
      }
      if (sbQuery != "")
        basicQueryStr = basicQueryStr + " where " + sbQuery;

      if ((sp.userName != "" && sp.userName != userName) || (sp.shopId != "" && sp.shopId != shopID))
        queryStrSelectUserAndShop = GetQueryStrSelectUsnAndShop(sp);

      //queryStrQQRelation = "select ProjectFileds.*,Shops.ShopName,Shops.ShopColor from ProjectFileds,Shops " + "where ProjectFileds.QQ in (select QQRelation.MainQQ from QQRelation " + "where QQRelation.QQ in (select ProjectFileds.QQ from ProjectFileds " + "where ProjectFileds.Date>'" + limitdate + "') " + "or QQRelation.MainQQ in (select ProjectFileds.QQ from ProjectFileds where ProjectFileds.Date>'" + limitdate + "')) " + "or ProjectFileds.QQ in ( " + "select QQRelation.QQ from QQRelation " + "where QQRelation.QQ in (select ProjectFileds.QQ from ProjectFileds " + "where ProjectFileds.Date>'" + limitdate + "') " + "or QQRelation.MainQQ in (select ProjectFileds.QQ from ProjectFileds where ProjectFileds.Date>'" + limitdate + "') " + ") and ProjectFileds.ShopID=Shops.ShopID";

      //noSourceSqlStr
      //if (queryStrSelectUserAndShop != "")
      //  noSourceSqlStr = basicQueryStrWithCondition + " intersect " + basicQueryStr + " intersect " + queryStrSelectUserAndShop + " intersect " + queryStrQQRelation;
      //else
      //  noSourceSqlStr = basicQueryStrWithCondition + " intersect " + basicQueryStr + " intersect " + queryStrQQRelation;
      string queryStr;
      sourceStr = " and ProjectFileds.Source like N'%" + sp.keywords + "%'";

      if (queryStrSelectUserAndShop != "")
      {
        noSourceSqlStr = basicQueryStrWithCondition + " intersect " + basicQueryStr + " intersect " + queryStrSelectUserAndShop;
        queryStr = basicQueryStrWithCondition + sourceStr + " intersect " + queryStrSelectUserAndShop;
      }
      else
      {
        noSourceSqlStr = basicQueryStrWithCondition + " intersect " + basicQueryStr;
        queryStr = basicQueryStrWithCondition + sourceStr;
      }

      //2014.3.24 Add Modify end
      //如果 isSourceQuery 被选中了则为“来源”进行模糊查询。
      if (sp.isSourceQuery == "true")
        lastSqlStr = queryStr;
      else
        lastSqlStr = noSourceSqlStr;

      //2014.09.01----start
      //主要用于当用户设置了“登记时间”的查询，需求是，当设置了天数，那么要在指定的日期内的QQ（及关联QQ）最新的QQ离今天的天数。。。
      //但是遇到了问题。。
      if (sp.limitdays > 0)
      {
        //string limitStr = "select ProjectFileds.*,Shops.ShopName,Shops.ShopColor from ProjectFileds,Shops where ProjectFileds.QQ in (select myouttable.QQ from (select mytable.QQ,datediff(day,mytable.mydate,getdate()) as daycount from (SELECT QQ, MAX(Date) AS mydate FROM ProjectFileds GROUP BY QQ) mytable) myouttable where myouttable.daycount>" + sp.limitdays + ") and  ProjectFileds.ShopID=Shops.ShopID";
        //string limitBasicStr = basicQueryStrWithCondition + " intersect " + "";
        //lastSqlStr = basicQueryStrWithCondition + " intersect " + limitStr;
      }

      //2014.09.01----end




      //======2014.06.12------
      //string getSQLString = NormalStoreProSQLStr(lastSqlStr, "*", "1=1", "Date", pagesize, page, "<(select min", " order by Date desc");
      //DataTable myTestDT = SQLHelper.ExecuteDt(getSQLString);
      //string strCount = "select count(*) from " + "(" + lastSqlStr + ")" + " as T1 " + " where 1=1";
      //count = SQLHelper.ExecuteScalar(strCount);
      //return myTestDT;
      //======2014.06.12------

      return GetAllRegisterByPagination(lastSqlStr, "*", "1=1", "Date", pagesize, page, out count).Tables[0];
    }
    public string NormalStoreProSQLStr(string customerSql, string SelectFieldName, string strWhere, string OrderFieldName, int PageSize, int PageIndex, string strTmp, string strOrder)
    {
      string strSQL = "select top " + PageSize + " " + SelectFieldName + " from " + " (" + customerSql + ") " + " as T1 " + " where " + OrderFieldName + @strTmp + "("
 + OrderFieldName + ") from (select top " + Convert.ToString((PageIndex - 1) * PageSize) + " " + OrderFieldName + " from " + " (" + @customerSql + ") as T2" + " where " + strWhere + " " + strOrder + ") as tblTmp) and " + strWhere + " " + @strOrder;
      if (PageIndex == 1)
      {
        strTmp = " where " + @strWhere;
        strSQL = "select top " + PageSize + " " + SelectFieldName + " from " + "(" + @customerSql + ")" + " as T1 " + @strTmp + " " + @strOrder;
      }
      return strSQL;
    }

    public List<string> GetAllRelationQQs(string qq)
    {
      QQRelationDal dal = new QQRelationDal();

      if (qq.Contains(','))
      {
        foreach (string q in qq.Split(','))
        {
          GetAllRelationQQs(q);
        }
      }
      else
      {
        DataTable dt = dal.getSameQQ(qq);
        if (dt.Rows.Count > 0)
        {
          for (int j = 0; j < dt.Rows.Count; j++)
          {
            if (!existQQList.Contains(dt.Rows[j]["MainQQ"].ToString()))
            {
              existQQList.Add(dt.Rows[j]["MainQQ"].ToString());
              GetAllRelationQQs(dt.Rows[j]["MainQQ"].ToString());
            }
            if (!existQQList.Contains(dt.Rows[j]["QQ"].ToString()))
            {
              existQQList.Add(dt.Rows[j]["QQ"].ToString());
              GetAllRelationQQs(dt.Rows[j]["QQ"].ToString());
            }
          }
        }
      }
      return existQQList;
    }
    private string GetQueryStrSelectUsnAndShop(SearchPara sp)
    {
      string tempQueryStrSelectUserAndShop = "";
      if (sp.userName != "" && sp.userName != userName && sp.shopId != "" && sp.shopId != shopID)
      {
        tempQueryStrSelectUserAndShop = "select ProjectFileds.*,Shops.ShopName,Shops.ShopColor from ProjectFileds inner join Shops on ProjectFileds.ShopID = Shops.ShopID where ProjectFileds.ShopID=" + sp.shopId + " and ProjectFileds.CreatedBy=N'" + sp.userName + "'";
      }
      if (sp.userName != "" && sp.userName != userName && sp.shopId == shopID)
      {
        tempQueryStrSelectUserAndShop = "select ProjectFileds.*,Shops.ShopName,Shops.ShopColor from  ProjectFileds inner join Shops on ProjectFileds.ShopID = Shops.ShopID where ProjectFileds.CreatedBy=N'" + sp.userName + "'";
      }
      if (sp.userName == userName && sp.shopId != shopID)
      {
        tempQueryStrSelectUserAndShop = "select ProjectFileds.*,Shops.ShopName,Shops.ShopColor from ProjectFileds inner join Shops on ProjectFileds.ShopID = Shops.ShopID where ProjectFileds.ShopID=" + sp.shopId + "";
      }
      return tempQueryStrSelectUserAndShop;
    }


    public DataTable GetRegister(string qq, int shopid, int limitdays)
    {
      string limitdate = DateTime.Now.AddDays(0 - limitdays).ToShortDateString();
      string strSql = "select * from ProjectFileds where QQ =@qq and Date>@limitdate and ShopID = @ShopID";
      SqlParameter[] para = new SqlParameter[]
            {
               new SqlParameter ("@qq", qq),
               new SqlParameter ("@limitdate", limitdate),      
               new SqlParameter ("@ShopID", shopid)      
            };
      return SQLHelper.ExecuteDt(strSql, para);
    }
    public DataTable GetRegister(string whereField, string fieldValue)
    {
      string strSql = "select * from ProjectFileds,Shops where " + whereField + "=@where and ProjectFileds.ShopID = Shops.ShopID";
      SqlParameter[] para = new SqlParameter[]
            {
               new SqlParameter ("@where", fieldValue)      
            };
      return SQLHelper.ExecuteDt(strSql, para);
    }

    public DataTable GetRegisterByPara(string key)
    {
      string strSql = "select * from ProjectFileds,Shops where ShopName Like @shopname and ProjectFileds.ShopID = Shops.ShopID";

      SqlParameter[] para = new SqlParameter[]
            {
               new SqlParameter ("@shopname","%"+key+"%")       
            };
      return SQLHelper.ExecuteDt(strSql, para);
    }

    public DataTable CopyRegister(int id, string username)
    {
      string strSql = "INSERT INTO ProjectFileds" +
"(QQ,Wangwang,ZhifubaoAccount,ZhifubaoName,RebateZhifubao,RebateZhifubaoName,CaifutongAccount," +
"CaifutongName,ShopID,Cash,Cellphone,Fixphone,ReceiveName,ReceiveAddress,ReceiveCellphone,ReceiveFixphone,Date,Province,City,District,Beizhu,BankAccount,BankName,CreatedBy,BankType,Source)" +
" select QQ, Wangwang,ZhifubaoAccount,ZhifubaoName,RebateZhifubao,RebateZhifubaoName,CaifutongAccount," +
" CaifutongName,ShopID,Cash,Cellphone,Fixphone,ReceiveName,ReceiveAddress,ReceiveCellphone,ReceiveFixphone," +
" GETDATE(),Province,City,District, Beizhu, BankAccount,BankName,CreatedBy=@CreatedBy,BankType,Source from ProjectFileds where ProID=@proid";
      SqlParameter[] para = new SqlParameter[]{
               new SqlParameter ("@proid",id),
               new SqlParameter("@CreatedBy",username)
            };
      return SQLHelper.ExecuteDt(strSql, para);
    }

    public int AddRegister(Register register)
    {
      string strSql = "Insert into ProjectFileds Values(@qq,@wangwang,@zhifubaoaccount,@zhifubaoname,@rebatezhifubao,@rebatezhifubaoname,@caifutongaccount,@caifutongname,@shopid,@cash,@cellphone,@fixphone,@receivename,@receiveaddress,@receivecellphone,@receivefixphone,@date,@province,@city,@district,@beizhu,@bankaccount,@bankname,@createdby,@banktype,@source,@QQRelation)";
      SqlParameter[] para = new SqlParameter[]{
               new SqlParameter ("@qq", register.Qq),   
               new SqlParameter ("@wangwang", register.Wangwang),   
               new SqlParameter ("@zhifubaoaccount", register.ZhifubaoAccount),    
               new SqlParameter ("@zhifubaoname", register.ZhifubaoName),  
               new SqlParameter ("@rebatezhifubao", register.RebateZhifubao),
               new SqlParameter ("@rebatezhifubaoname", register.RebateZhifubaoName),  
               new SqlParameter ("@caifutongaccount", register.CaifutongAccount),   
               new SqlParameter ("@caifutongname", register.CaifutongName),   
               new SqlParameter ("@shopid", register.ShopId),   
               new SqlParameter ("@cash", register.Cash),   
               new SqlParameter ("@cellphone", register.CellPhone),  
               new SqlParameter ("@fixphone", register.FixPhone),  
               new SqlParameter ("@receivename", register.ReceiveName),
               new SqlParameter ("@receiveaddress", register.ReceiveAddress),        
               new SqlParameter ("@receivecellphone", register.ReceiveCellphone),  
               new SqlParameter ("@receivefixphone", register.ReceiveFixphone),
               new SqlParameter ("@date",register.Date),
               new SqlParameter ("@province", register.Province),
               new SqlParameter ("@city", register.City),
               new SqlParameter ("@district", register.District),
               new SqlParameter ("@beizhu", register.Beizhu),
               new SqlParameter ("@bankaccount", register.BankAccount),
               new SqlParameter ("@bankname", register.BankName),
               new SqlParameter ("@banktype", register.BankType),
               new SqlParameter ("@source", register.Source),
               new SqlParameter ("@createdby", register.CreatedBy),
               new SqlParameter("@QQRelation",register.QQRelation)

            };


      return SQLHelper.ExecuteDt(strSql, para).Rows.Count;

    }


    public DataTable GetRecentRegister(string wangwang, string shopname)
    {
      string strSql = "select * from ProjectFileds,Shops where ShopName = @shopname and Wangwang=@wangwang and ProjectFileds.ShopID = Shops.ShopID order by Date desc";
      SqlParameter[] para = new SqlParameter[]{
               new SqlParameter ("@wangwang", wangwang),
               new SqlParameter ("@shopname", shopname)
            };
      return SQLHelper.ExecuteDt(strSql, para);

    }

    public int UpdateRegister(Register register, string whereField, string value)
    {
      string str = "Update ProjectFileds set ";

      IList<SqlParameter> sqlParaList = new List<SqlParameter>();

      string strSql = "";
      if (!string.IsNullOrEmpty(register.Qq))
      {
        strSql += "QQ=@qq,";
        SqlParameter qq = new SqlParameter("@qq", register.Qq);
        sqlParaList.Add(qq);
      }
      if (!string.IsNullOrEmpty(register.Wangwang))
      {
        strSql += "Wangwang=@wangwang,";
        SqlParameter wangwang = new SqlParameter("@wangwang", register.Wangwang);
        sqlParaList.Add(wangwang);
      }
      if (!string.IsNullOrEmpty(register.ZhifubaoAccount))
      {
        strSql += "ZhifubaoAccount=@zhifubaoaccount,";
        SqlParameter zhifubaoaccount = new SqlParameter("@zhifubaoaccount", register.ZhifubaoAccount);
        sqlParaList.Add(zhifubaoaccount);
      }
      if (!string.IsNullOrEmpty(register.ZhifubaoName))
      {
        strSql += "ZhifubaoName=@zhifubaoname,";
        SqlParameter zhifubaoname = new SqlParameter("@zhifubaoname", register.ZhifubaoName);
        sqlParaList.Add(zhifubaoname);
      }
      if (!string.IsNullOrEmpty(register.RebateZhifubao))
      {
        strSql += "RebateZhifubao=@rebatezhifubao,";

        SqlParameter rebatezhifubao = new SqlParameter("@rebatezhifubao", register.RebateZhifubao);
        sqlParaList.Add(rebatezhifubao);
      }
      if (!string.IsNullOrEmpty(register.RebateZhifubaoName))
      {
        strSql += "RebateZhifubaoName=@rebatezhifubaoname,";
        SqlParameter rebatezhifubaoname = new SqlParameter("@rebatezhifubaoname", register.RebateZhifubaoName);
        sqlParaList.Add(rebatezhifubaoname);
      }
      if (!string.IsNullOrEmpty(register.CaifutongAccount))
      {
        strSql += "CaifutongAccount=@caifutongaccount,";
        SqlParameter caifutongaccount = new SqlParameter("@caifutongaccount", register.CaifutongAccount);
        sqlParaList.Add(caifutongaccount);
      }
      if (!string.IsNullOrEmpty(register.CaifutongName))
      {
        strSql += "CaifutongName=@caifutongname,";
        SqlParameter caifutongname = new SqlParameter("@caifutongname", register.CaifutongName);
        sqlParaList.Add(caifutongname);
      }
      if (register.ShopId != 0)
      {
        strSql += "ShopID=@shopid,";
        SqlParameter shopid = new SqlParameter("@shopid", register.ShopId);
        sqlParaList.Add(shopid);
      }

      strSql += "Cash=@cash,";
      SqlParameter cash = new SqlParameter("@cash", register.Cash);
      sqlParaList.Add(cash);

      strSql += "Cellphone=@cellphone,";
      SqlParameter cellphone = new SqlParameter("@cellphone", register.CellPhone);
      sqlParaList.Add(cellphone);

      strSql += "Fixphone=@fixphone,";
      SqlParameter fixphone = new SqlParameter("@fixphone", register.FixPhone);
      sqlParaList.Add(fixphone);

      strSql += "ReceiveName=@receivename,";
      SqlParameter receivename = new SqlParameter("@receivename", register.ReceiveName);
      sqlParaList.Add(receivename);

      strSql += "ReceiveAddress=@receiveaddress,";
      SqlParameter receiveaddress = new SqlParameter("@receiveaddress", register.ReceiveAddress);
      sqlParaList.Add(receiveaddress);

      strSql += "ReceiveCellphone=@receivecellphone,";
      SqlParameter receivecellphone = new SqlParameter("@receivecellphone", register.ReceiveCellphone);
      sqlParaList.Add(receivecellphone);

      strSql += "ReceiveFixphone=@receivefixphone,";
      SqlParameter receivefixphone = new SqlParameter("@receivefixphone", register.ReceiveFixphone);
      sqlParaList.Add(receivefixphone);

      if (!string.IsNullOrEmpty(register.Date))
      {
        strSql += "Date=@date,";
        SqlParameter date = new SqlParameter("@date", register.Date);
        sqlParaList.Add(date);
      }

      if (!string.IsNullOrEmpty(register.Province))
      {
        strSql += "Province=@province,";
        SqlParameter province = new SqlParameter("@province", register.Province);
        sqlParaList.Add(province);
      } if (!string.IsNullOrEmpty(register.City))
      {
        strSql += "City=@city,";
        SqlParameter city = new SqlParameter("@city", register.City);
        sqlParaList.Add(city);
      }

      strSql += "District=@district,";
      SqlParameter district = new SqlParameter("@district", register.District);
      sqlParaList.Add(district);

      strSql += "Beizhu=@beizhu,";
      SqlParameter beizhu = new SqlParameter("@beizhu", register.Beizhu);
      sqlParaList.Add(beizhu);

      strSql += "BankAccount=@bankaccount,";
      SqlParameter bankaccount = new SqlParameter("@bankaccount", register.BankAccount);
      sqlParaList.Add(bankaccount);

      strSql += "BankName=@bankname,";
      SqlParameter bankname = new SqlParameter("@bankname", register.BankName);
      sqlParaList.Add(bankname);

      strSql += "BankType=@banktype,";
      SqlParameter banktype = new SqlParameter("@banktype", register.BankType);
      sqlParaList.Add(banktype);

      strSql += "Source=@source,";
      SqlParameter source = new SqlParameter("@source", register.Source);
      sqlParaList.Add(source);

      strSql += "QQRelation=@qqrelation,";
      SqlParameter sourceQQRelation = new SqlParameter("@qqrelation", register.QQRelation);
      sqlParaList.Add(sourceQQRelation);



      string strWhere = " where " + whereField + "=@where";

      if (!string.IsNullOrEmpty(strSql))
      {
        strSql = strSql.Substring(0, strSql.Length - 1);

        SqlParameter where = new SqlParameter("@where", value);
        sqlParaList.Add(where);
        strSql = str + strSql + strWhere;
      }
      else
      {
        return 0;
      }

      SqlParameter[] para = sqlParaList.ToArray();
      return SQLHelper.ExecuteSql(strSql, para);
    }

    public int DeleteRegister(string proId)
    {
      string strSql = "Delete ProjectFileds where ProID = @proid";
      SqlParameter[] para = new SqlParameter[]
            {
               new SqlParameter ("@proid", proId)
            };
      return SQLHelper.ExecuteSql(strSql, para);
    }


    public bool isValidDate(string qq, string limitdays)
    {

      string strSql = "select * from ProjectFileds where ProjectFileds.Date>@limitdate and qq=@qq";
      SqlParameter[] para = new SqlParameter[]
            {
               new SqlParameter ("@limitdate", limitdays),
               new SqlParameter ("@qq", qq)            
            };
      DataTable dt = SQLHelper.ExecuteDt(strSql, para);
      //未超出时间
      if (dt.Rows.Count > 0)
      {
        return false;
      }
      //超出
      return true;
    }

    public DataTable GetRegisterDataBySql(string strSql)
    {
      return SQLHelper.ExecuteDt(strSql);
    }


  }
}
